import sqlite3 as sql

class iamadb:
    def __init__(self, path):
        self.path = path
    
    def __enter__(self):
        self.conn = sql.connect(self.path)
        self.cursor = self.conn.cursor()
        return self
        
    def __exit__(self, type, value, traceback):
        self.cursor = None
        self.conn.close()
        
    def getThread(self, threadid):
        self.cursor.execute("""SELECT thread.id, title, author.name, body, posted, last_update, url FROM thread
        INNER JOIN author ON thread.author = author.id WHERE thread.id = ?""", [threadid])
        row = self.cursor.fetchone()
        if row is not None:
            return thread(row)
        else:
            return None
            
    def getAllThreads(self):
        threads = []
        self.cursor.execute("""SELECT thread.id, title, author.name, body, posted, last_update, url FROM thread
        INNER JOIN author ON thread.author = author.id order by posted ASC""")
        while True:
            row = self.cursor.fetchone()
            if row is None:
                return threads;
            threads.append(thread(row))
            
    def getConversations(self, threadid):
        conversations = []
        self.cursor.execute("""SELECT conversation.id, conversation.thread, q_id, qauthor.name, q_body, q_posted, a_id, aauthor.name, a_body, a_posted
        FROM conversation INNER JOIN author as qauthor ON conversation.q_author = qauthor.id
        INNER JOIN author as aauthor on conversation.a_author = aauthor.id WHERE thread = ? ORDER BY conversation.q_posted""", [threadid])
        while True:
            row = self.cursor.fetchone()
            if row is None:
                return conversations;
            conversations.append(conversation(row))
        
class thread:
    def __init__(self, row=None):
        if row is not None:
            self.id = row[0]
            self.title = row[1]
            self.author = row[2]
            self.body = row[3]
            self.posted = row[4]
            self.last_update = row[5]
            self.url = row[6]
            self.isnew = False
        else:
            self.isnew = True
            
    def save(self, db):
        exists = db.cursor.execute("""SELECT id FROM thread WHERE id = ?""", [self.id]).fetchone() != None
        a = author(name=self.author)
        a.save(db)
        if exists:
            db.cursor.execute("""UPDATE thread
            SET title = ?,
            author = ?,
            body = ?,
            posted = ?,
            last_update = ?
            WHERE id = ?""", [self.title, a.id, self.body, self.posted, self.last_update, self.id])
        else:
            db.cursor.execute("""INSERT INTO thread
            VALUES(?,?,?,?,?,?,?)""", [self.id, self.title, a.id, self.body, self.posted, self.last_update, self.url])
        db.conn.commit()
        
class conversation:
    def __init__(self, row=None):
        if row is not None:
            self.id = row[0]
            self.thread = row[1]
            self.q_id = row[2]
            self.q_author = row[3]
            self.q_body = row[4]
            self.q_posted = row[5]
            self.a_id = row[6]
            self.a_author = row[7]
            self.a_body = row[8]
            self.a_posted = row[9]
            self.isnew = False
        else:
            self.isnew = True
            
    def save(self, db):
        row = db.cursor.execute("""SELECT id FROM conversation WHERE q_id = ? AND a_id = ?""", [self.q_id, self.a_id]).fetchone()
        qauthor = author(name=self.q_author)
        qauthor.save(db)
        aauthor = author(name=self.a_author)
        aauthor.save(db)
        if row is not None:
            db.cursor.execute("""UPDATE conversation
            SET thread = ?,
            q_id = ?,
            q_author = ?,
            q_body = ?,
            q_posted = ?,
            a_id = ?,
            a_author = ?,
            a_body = ?,
            a_posted = ?
            WHERE id = ?""", [self.thread, self.q_id, qauthor.id, self.q_body, self.q_posted, self.a_id, aauthor.id, self.a_body, self.a_posted, row[0]])
        else:
            db.cursor.execute("""INSERT INTO conversation
            VALUES(null, ?, ?, ?, ?, ?, ?, ?, ?, ?)""", [self.thread, self.q_id, qauthor.id, self.q_body, self.q_posted, self.a_id, aauthor.id, self.a_body, self.a_posted])
        db.conn.commit()    
            
class author:
    def __init__(self, row=None, name=None):
        if row is not None:
            self.id = row[0]
            self.name = row[1]
            self.isnew = False
        else:
            self.isnew = True
            if name is not None:
                self.name = name
                
    def save(self, db):
        authorrow = db.cursor.execute("""SELECT id FROM author WHERE name = ?""", [self.name]).fetchone()
        if authorrow is None:
            db.cursor.execute("""INSERT INTO author VALUES(null, ?)""", [self.name])
            db.conn.commit()
            self.id = db.cursor.lastrowid
        else:
            self.id = authorrow[0]
    